﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Table_Dal;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DeThiDal:DataAccessLayer
    {
        DataDataContext dt;
        public List<DeThiTbl> LayDanhSachDeThi_MaMonHoc(string MaMonHoc)
        {
            dt = GetKetnoi();
            IEnumerable<DETHI> q = (from i in dt.MONHOCs
                                     join u in dt.CHUONGs on i.MAMONHOC equals u.MAMONHOC
                                     join j in dt.CAUHOIs on u.MACHUONG equals j.MACHUONG
                                     join z in dt.CHITIETDETHIs on j.MACAUHOI equals z.MACAUHOI
                                     join x in dt.DETHIs on z.MADETHI equals x.MADETHI
                                     where u.MAMONHOC ==MaMonHoc
                                     select x).Distinct();
            List<DeThiTbl> dsmh = new List<DeThiTbl>();
            foreach (DETHI item in q)
            {
                DeThiTbl mh = new DeThiTbl();
                mh.MADETHI = item.MADETHI;
                mh.TENDETHI = item.TENDETHI;
                mh.MANGUOIRADE = item.MANGUOIRADE;
                mh.SOCAUHOI = int.Parse( item.SOCAUHOI.ToString());
                mh.THOIGIAN = int.Parse(item.THOIGIAN.ToString());
                mh.NGAYTAODE = DateTime.Parse( item.NGAYTAODE.ToString());
                dsmh.Add(mh);
            }
            return dsmh;
        }

        public List<DeThiTbl> LayDanhSachDeThi_MaMonHoc(string MaMonHoc,string mssv)
        {
            dt = GetKetnoi();
            IEnumerable<DETHI> q = (from i in dt.MONHOCs
                                    join u in dt.CHUONGs on i.MAMONHOC equals u.MAMONHOC
                                    join j in dt.CAUHOIs on u.MACHUONG equals j.MACHUONG
                                    join z in dt.CHITIETDETHIs on j.MACAUHOI equals z.MACAUHOI
                                    join x in dt.DETHIs on z.MADETHI equals x.MADETHI
                                    join y in dt.DIEMs on x.MADETHI equals y.MADETHI
                                    where y.MSSV == mssv && u.MAMONHOC == MaMonHoc
                                    select x).Distinct();
            List<DeThiTbl> dsmh = new List<DeThiTbl>();
            foreach (DETHI item in q)
            {
                DeThiTbl mh = new DeThiTbl();
                mh.MADETHI = item.MADETHI;
                mh.TENDETHI = item.TENDETHI;
                mh.MANGUOIRADE = item.MANGUOIRADE;
                mh.SOCAUHOI = int.Parse( item.SOCAUHOI.ToString());
                mh.THOIGIAN = int.Parse(item.THOIGIAN.ToString());
                mh.NGAYTAODE = DateTime.Parse( item.NGAYTAODE.ToString());
                dsmh.Add(mh);
            }
            return dsmh;
        }
        public DeThiTbl ThongTinDeThi(string Madethi)
        {
            dt = GetKetnoi();
            DETHI dthi = dt.DETHIs.SingleOrDefault(i => i.MADETHI == Madethi);
            DeThiTbl dttbl = new DeThiTbl();
            dttbl.MADETHI = Madethi;
            dttbl.TENDETHI = dthi.TENDETHI;
            dttbl.THOIGIAN = int.Parse(dthi.SOCAUHOI.ToString());
            dttbl.NGAYTAODE = DateTime.Parse(dthi.NGAYTAODE.ToString());
            dttbl.SOCAUHOI = int.Parse(dthi.SOCAUHOI.ToString());
            dttbl.MANGUOIRADE = dthi.MANGUOIRADE;
            return dttbl;
        }
        public bool XoaDeThi(string Madethi)
        {
            try
            {
                dt = GetKetnoi();
                DETHI dthi = dt.DETHIs.SingleOrDefault(i => i.MADETHI == Madethi);
                dt.DETHIs.DeleteOnSubmit(dthi);
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
        public bool ThemDeThi(DeThiTbl dethi)
        {
            try
            {
                dt = GetKetnoi();
                DETHI dthi = new DETHI();
                dthi.MADETHI=  dethi.MADETHI;
                dthi.TENDETHI=dethi.TENDETHI;
                dthi.MANGUOIRADE = dethi.MANGUOIRADE;
                dthi.SOCAUHOI = dethi.SOCAUHOI;
                dthi.THOIGIAN = dethi.THOIGIAN;
                dthi.NGAYTAODE = dethi.NGAYTAODE;
                dt.DETHIs.InsertOnSubmit(dthi);
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
        
        public List<DeThiTbl> LayTatCaDeThi()
        {
            dt = GetKetnoi();
            IEnumerable<DETHI> q = from i in dt.DETHIs
                                   select i;
            List<DeThiTbl> lst = new List<DeThiTbl>();
            foreach (DETHI i in q)
            {
                DeThiTbl ctdt = new DeThiTbl();
                ctdt.MADETHI = i.MADETHI;
                ctdt.TENDETHI = i.TENDETHI;
                ctdt.SOCAUHOI = int.Parse(i.SOCAUHOI.ToString());
                lst.Add(ctdt);
            }
            return lst;
        }
        public bool KiemTra_TrungKhoa(string Ma)
        {
            dt = GetKetnoi();
            IEnumerable<DETHI> q = dt.DETHIs.Where(k => k.MADETHI == Ma);
            foreach (DETHI item in q)
            {
                if (item.MADETHI == Ma)
                    return true;
            }
            return false;
        }

        public void XoaDethiTheo_MaCauHoi(string p)
        {
            dt = GetKetnoi();
            IEnumerable<DETHI> q = (from i in dt.DETHIs
                                   join j in dt.CHITIETDETHIs on i.MADETHI equals j.MADETHI
                                   where j.MACAUHOI == p
                                   select i).Distinct();
            dt.DETHIs.DeleteAllOnSubmit(q);
            dt.SubmitChanges();
        }
    }
}
